package com.g2room.myuall.grap.univ;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;

import com.g2room.myuall.grap.univ.po.forum.CatMapping;
import com.g2room.myuall.grap.univ.po.forum.Forum;
import com.g2room.myuall.grap.univ.po.forum.Images;
import com.g2room.myuall.grap.univ.po.forum.Post;
import com.g2room.myuall.util.Constants;
import com.g2room.myuall.util.DbUtil;
import com.g2room.myuall.util.DbUtils;
import com.g2room.myuall.util.EncryptUtil;
import com.g2room.myuall.util.UBBDecoder;

public class TimeGrapBbs implements Runnable {
	private static Map<Integer, List<CatMapping>> cms;
	List<Forum> forums;
	static Connection connection = null;
	
	TimeGrapBbs(List<Forum> fs) {
		this.forums = fs;
	}
	public static void main(String[] args) throws Exception{
		grapArtis();
    }
	
	public static void grapArtis() {
		System.out.println(new Date() + "开始抓取BBS数据！");
		DbUtil.closeConn(connection);
		// 获取连接
		connection = DbUtil.getMyuallConn();
		
		// 从数据库获取论坛配置数据
		List<Forum> forums = getAllEnableForum();
		cms = getCatMapping();
		// 分为多个线程去跑
		int start = 0;
		List<List<Forum>> forumses = new  ArrayList<List<Forum>>();
		for(int i = 0; i < forums.size(); i ++) {
			if(i>0 && i % 15 ==0) {
				forumses.add(forums.subList(start, i));
				start = i;
			}
		}
		// 处理未能除尽的部分
		if(start < forums.size()) {
			forumses.add(forums.subList(start, forums.size()));
		}
		
		for(List<Forum> fs : forumses) {
			TimeGrapBbs gua = new TimeGrapBbs(fs);
			Thread t = new Thread(gua);
			t.start();
		}
	}

	@Override
	public void run() {
		sync();
	}

	public void sync() {
		System.out.println("开始同步论坛数据，本次同步论坛数：" + forums.size());
		// 循环获取数据填充
		for(Forum forum : forums) {
			//TODO 改为多线程
			System.out.println("开始同步" + forum.getName() + "论坛的数据");
			Long startTime;
			if(null == forum.getSyncTime() || 0 == forum.getSyncTime()) {
				// 默认从当前时间前一个月开始的数据
				Calendar cal = Calendar.getInstance();
				cal.set(Calendar.MONTH, cal.get(Calendar.MONTH) - 3);
				startTime = cal.getTime().getTime() / 1000;
//				startTime =1033280000l;
			} else {
				startTime = forum.getSyncTime();
			}
			
			Long endTime = new Date().getTime()/1000;
			System.out.println("开始同步从 " 
				+ new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date(startTime * 1000))
				+ " 到 " 
				+ new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date(endTime * 1000))
				+ "的数据！");
			// 获取数据库连接信息
			String dbPwd = forum.getDbPwd();
			if (StringUtils.isNotEmpty(dbPwd)) {
				dbPwd = EncryptUtil.decrypt(dbPwd, EncryptUtil.KEY_DBPWD);
			}
			
//			dbPwd = "ab89216035ab";
			Connection conn = null;
			Statement statement = null;
			try {
				conn = DbUtils.getConn(forum.getDbUrl(), forum.getDbUsername(), dbPwd);
				statement = conn.createStatement();
				
				// 记录每一个论坛的美图共赏的原对应模块ID
				CatMapping imageOldCat = null;
				try {
					Map<Integer, CatMapping> catMap = new HashMap<Integer, CatMapping>();
					// 获取映射模块的ID，根据ID获取数据并插入
					for(CatMapping cm : cms.get(forum.getId())) {
						if(cm.getCatId() == Constants.CAT_IMAGE_ID) {
							imageOldCat = cm;
							continue;
						}
						catMap.put(cm.getOldCatId(), cm);
					}
					insertPosts(forum, catMap, statement, startTime, endTime);
				} catch (Exception e) {
					e.printStackTrace();
					System.out.println("同步论坛" + forum.getName()  + "的模块数据异常：" + e.getMessage());				
				}
				
				try {
					if(null != imageOldCat && null != imageOldCat.getCatId()) {
						System.out.println("开始同步图片数据！");
						for(int i = 0; i < 10; i++) {
							insertImages(forum, imageOldCat, statement, startTime, endTime, i);
						}
					}
				} catch (Exception e) {
					e.printStackTrace();
					System.out.println("同步图片数据错误：" + e.getMessage());
				}
				
			} catch (Exception e) {
				e.printStackTrace();
				System.out.println("同步" + forum.getName() + "论坛的数据错误：" + e.getMessage());
			} finally {
				try {
					if(null != statement) {
						statement.close();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
				try {
					if(null != conn) {
						conn.close();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			System.out.println("开始更新同步时间为"  + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date(endTime * 1000)) );
			// 更新同步时间
			updateSyncTime(endTime, forum.getId());
			System.out.println("更新同步时间完成");
		}
		System.out.println("本次同步完成");
	}
	

	public static List<Forum> getAllEnableForum() {
		List<Forum> forums = new ArrayList<Forum>();
		try {
			Statement statement = connection.createStatement();
			String sql = "select * from tog_forum where isEnable=0";
			ResultSet rs = statement.executeQuery(sql);
			while(rs.next()) {
				Forum forum = new Forum();
				forum.setId(rs.getInt("id"));
				forum.setCity(rs.getInt("city"));
				forum.setName(rs.getString("name"));
				forum.setUrl(rs.getString("url"));
				forum.setDbUsername(rs.getString("dbUsername"));
				forum.setDbUrl(rs.getString("dbUrl"));
				forum.setDbPwd(rs.getString("dbPwd"));
				forum.setCityName(rs.getString("cityName"));
				forum.setProvince(rs.getInt("province"));
				forum.setProvinceName(rs.getString("provinceName"));
				forum.setSyncTime(rs.getLong("syncTime"));
				
				forums.add(forum);
			}
			rs.close();
			statement.close();
		} catch(Exception e) {
			e.printStackTrace();
		}
		return forums;
	}
	
	public static Map<Integer, List<CatMapping>> getCatMapping() {
		// KEY为forumID
		Map<Integer, List<CatMapping>> cmMap = new HashMap<Integer, List<CatMapping>>();
		try {
			Statement statement = connection.createStatement();
			String sql = "select * from tog_cat_mapping";
			ResultSet rs = statement.executeQuery(sql);
			while(rs.next()) {
				CatMapping cm = new CatMapping();
				cm.setId(rs.getInt("id"));
				cm.setCatId(rs.getInt("catId"));
				cm.setCatName(rs.getString("catName"));
				cm.setOldCatId(rs.getInt("oldCatId"));
				cm.setOldCatName(rs.getString("oldCatName"));
				cm.setUrl(rs.getString("url"));
				cm.setForumId(rs.getInt("forumId"));
				
				List<CatMapping> cms = cmMap.get(cm.getForumId());
				if(null == cms) cms = new ArrayList<CatMapping>();
				cms.add(cm);
				cmMap.put(cm.getForumId(), cms);
			}
			rs.close();
			statement.close();
		} catch(Exception e) {
			e.printStackTrace();
		}
		return cmMap;
	}
	
	public void updateSyncTime(long time, int id) {
		try {
			PreparedStatement grapPs = connection.prepareStatement("update `tog_forum` set syncTime=? where id=? ");
			grapPs.setLong(1, time);
			grapPs.setInt(2, id);
			grapPs.execute();
		} catch(Exception e) {
			e.printStackTrace();
		}
	}
	

	/**
	 * 查询并插入主题数据到Post表
	 * @param forum	论坛类
	 * @param cm	类别映射类
	 * @param statement	
	 * @param startTime
	 * @param endTime
	 * @throws SQLException
	 */
	public void insertPosts(Forum forum, Map<Integer, CatMapping> catMap, Statement statement,
			Long startTime, Long endTime) throws SQLException {
		String querySql = "SELECT t.*,p.message FROM pre_forum_thread t inner join pre_forum_post p on p.tid=t.tid " +
				"where p.first = 1 and t.dateline>'" + startTime + "' and t.dateline<='" +endTime+"' and t.displayorder>=0";
		ResultSet rs = statement.executeQuery(querySql);
		List<Post> posts  = new LinkedList<Post>();
		System.out.println("本次同步SQL语句：" + querySql);

		while (rs.next()) {
			Integer oldCatId = rs.getInt("fid");
			if(null == oldCatId) {
				System.out.println("oldCatId（"+oldCatId+"） is null，论坛="+forum.getName());
				continue;
			}
			CatMapping cm = catMap.get(oldCatId);
			if(null == cm) {
				System.out.println("oldCatId（"+oldCatId+"） 没有做类型映射，论坛="+forum.getName());
				continue;
			}
			Post post = new Post();
			// and t.fid=" + cm.getOldCatId() + " 
			post.setForumId(forum.getId());
			post.setForumName(forum.getName());
			post.setForumUrl(forum.getUrl());
			post.setProvince(forum.getProvince());
			post.setProvinceName(forum.getProvinceName());
			post.setCity(forum.getCity());
			post.setCityName(forum.getCityName());
			
			post.setCategoryId(cm.getCatId());
			post.setCategoryName(cm.getCatName());
			
			post.setStatus(Constants.POST_UNAUDIT);
			post.setShowSummary(Constants.STATUS_HIDDEN);
			
			post.setAuthor(DbUtils.charset(rs.getString("author")));
			post.setAuthorId(rs.getInt("authorid"));
			post.setAuthorUrl(getAuthorUrl(forum.getUrl(), post.getAuthorId()));
			// 转换Unix秒单位的时间为正常时间
			post.setDateline(new Date(rs.getLong("dateline") * 1000));
			post.setDigest(rs.getInt("digest"));
			//TODO 要处理
			post.setStick(rs.getInt("displayOrder"));
			post.setFid(rs.getInt("fid"));
			post.setHighlight(rs.getInt("highlight"));
			post.setOldStatus(rs.getInt("status"));
//			post.setOldSubject(rs.getString("subject"));
			post.setReplies(rs.getInt("replies"));
			post.setSubject(DbUtils.charset(rs.getString("subject")));
			
			post.setMessage(getUbbStr(rs.getString("message")));
			post.setViews(rs.getInt("views"));
			
			Integer tid = rs.getInt("tid");
			post.setTid(tid);
			post.setUrl(getPostUrl(forum.getUrl(), post.getTid()));
			posts.add(post);
		}
		
		rs.close();
		// 批量插入到数据库表
		if(null == posts || posts.size() == 0) {
			System.out.println("没有插入数据库的数据！");
			return;
		}
		System.out.println("开始插入到数据库，数据量为：" + posts.size());
		insertPostBatch(posts);
		System.out.println("插入成功。");
	}
	
	public static void insertPostBatch(List<Post> posts) {
		try {
			String sql = "insert into tog_post (categoryId,categoryName,fid,authorId,authorUrl,views,oldSubject," //7
					+ "subject,tid,dateline,status,oldStatus,author,replies,highlight,stick,url,digest,forumId,forumName," // 19
					+ "forumUrl,province,provinceName,city,cityName,showSummary,summary,syncTime,message) values (" 
					+ "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,now(),?)";
			PreparedStatement ps = connection.prepareStatement(sql);
			for(Post p : posts) {
				ps.setInt(1, p.getCategoryId());
				ps.setString(2, p.getCategoryName());
				ps.setInt(3, p.getFid());
				ps.setInt(4, p.getAuthorId());
				ps.setString(5, p.getAuthorUrl());
				ps.setInt(6, p.getViews());
				ps.setString(7, p.getOldSubject());
				
				ps.setString(8, p.getSubject());
				ps.setInt(9, p.getTid());
				ps.setDate(10, new java.sql.Date(p.getDateline().getTime()));
//				ps.setDate(10, (java.sql.Date) p.getDateline());
				ps.setInt(11,  p.getStatus());
				ps.setInt(12, p.getOldStatus());
				ps.setString(13, p.getAuthor());
				ps.setInt(14, p.getReplies());
				ps.setInt(15, p.getHighlight());
				ps.setInt(16, p.getStick());
				ps.setString(17, p.getUrl());
				ps.setInt(18, p.getDigest());
				ps.setInt(19, p.getForumId());
				ps.setString(20, p.getForumName());
				
				ps.setString(21, p.getForumUrl());
				ps.setInt(22, p.getProvince());
				ps.setString(23, p.getProvinceName());
				ps.setInt(24, p.getCity());
				ps.setString(25, p.getCityName());
				ps.setInt(26, p.getShowSummary());
				ps.setString(27, p.getSummary());
				ps.setString(28, p.getMessage());
				ps.addBatch();
			}
			ps.execute();
		} catch(Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 查询并插入图片数据到Images表
	 * @param forum	论坛类
	 * @param cm	类别映射类
	 * @param statement	
	 * @param startTime
	 * @param endTime
	 * @param i	附件分表索引，取值0~9
	 * @throws SQLException
	 */
	public void insertImages(Forum forum, CatMapping cm, Statement statement,
			Long startTime, Long endTime, int i) throws SQLException {
		String querySql = "select a.tid,a.attachment,a.dateline,p.subject,p.authorid,p.author,p.message from pre_forum_attachment_" + i + 
				" a inner join pre_forum_post p ON p.pid=a.pid  where p.invisible=0 and p.fid= " + cm.getOldCatId() +
				" and a.isimage=1 and a.dateline>" + startTime+" and a.dateline<="+endTime;
		
		ResultSet rs = statement.executeQuery(querySql);
		List<Images> images  = new LinkedList<Images>();
		System.out.println("本次同步图片表" + i + "语句：" + querySql);
		while (rs.next()) {
			Images image = new Images();
			image.setForumId(forum.getId());
			image.setForumName(forum.getName());
			image.setForumUrl(forum.getUrl());
			image.setProvince(forum.getProvince());
			image.setProvinceName(forum.getProvinceName());
			image.setCity(forum.getCity());
			image.setCityName(forum.getCityName());
			
			image.setCatId(cm.getCatId());
			image.setCatName(cm.getCatName());
			
			image.setStatus(Constants.STATUS_NORMAL);

			image.setAuthor(DbUtils.charset(rs.getString("author")));
			image.setAuthorId(rs.getInt("authorid"));
			image.setAuthorUrl(getAuthorUrl(forum.getUrl(), image.getAuthorId()));
			image.setUrl(getPostUrl(forum.getUrl(), rs.getInt("tid")));
			image.setImageUrl(getImageUrl(forum.getUrl(), rs.getString("attachment")));
			image.setUpdateTime(new Date(rs.getLong("dateline") * 1000));
			image.setTitle(DbUtils.charset(rs.getString("subject")));
			image.setMessage(getUbbStr(rs.getString("message")));
			image.setTid(rs.getInt("tid"));
			images.add(image);
		}
		
		rs.close();
		// 批量插入到数据库表
		if(null == images || images.size() == 0)  {
			System.out.println("没有插入数据库的数据！");
			return;
		}
		System.out.println("开始插入图片数据到数据库，数据量为：" + images.size());
		insertImagesBatch(images);
		System.out.println("插入图片到数据库成功。");
	}
	

	public static void insertImagesBatch(List<Images> images) {
		try {
			String sql = "insert into tog_images (tid,catId,catName,forumId,forumName,title,status,updateTime,imageUrl," //7
					+ "url,forumUrl,province,provinceName,city,cityName,authorId,author,authorUrl,syncTime,message) " // 19
					+ "values (" 
					+ "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,now(),?)";
			PreparedStatement ps = connection.prepareStatement(sql);
			for(Images img : images) {
				ps.setInt(1, img.getTid());
				ps.setInt(2, img.getCatId());
				ps.setString(3, img.getCatName());
				ps.setInt(4, img.getForumId());
				ps.setString(5, img.getForumName());
				ps.setString(6, img.getTitle());
				ps.setInt(7,  img.getStatus());
				ps.setDate(8, new java.sql.Date(img.getUpdateTime().getTime()));
//				ps.setDate(8, (java.sql.Date) img.getUpdateTime());
				ps.setString(9, img.getImageUrl());
				ps.setString(10, img.getUrl());
				ps.setString(11, img.getForumUrl());
				ps.setInt(12, img.getProvince());
				ps.setString(13, img.getProvinceName());
				ps.setInt(14, img.getCity());
				ps.setString(15, img.getCityName());
//				ps.setInt(16, img.getPosition());
				ps.setInt(16, img.getAuthorId());
				ps.setString(17, img.getAuthor());
				ps.setString(18, img.getAuthorUrl());
				ps.setString(19, img.getMessage());
				ps.addBatch();
			}
			ps.execute();
		} catch(Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 获取作者的链接地址
	 * @param forumUrl
	 * @param uId
	 * @return
	 */
	private String getAuthorUrl(String forumUrl, Integer uId) {
		//http://www.bnuren.com/home.php?mod=space&uid=
		return forumUrl + "/home.php?mod=space&uid=" + uId;
	}
	
	/**
	 * 获取主题的链接地址
	 * @param forumUrl
	 * @param tid
	 * @return
	 */
	private String getPostUrl(String forumUrl, Integer tid) {
		//http://ahpu.myubbs.com/thread-133220-1-1.html
		return forumUrl + "/thread-" + tid +"-1-1.html";
	}
	
	/**
	 * 获取图片的链接地址
	 * @param forumUrl		论坛访问地址
	 * @param attachment	附件相对路径
	 * @return
	 */
	private String getImageUrl(String forumUrl, String attachment) {
		return forumUrl + "/data/attachment/forum/" + attachment;
	}
	
	/**
	 * 对数据库messge字段内容进行UBB和替换换行处理
	 * @param str
	 * @return
	 */
	private String getUbbStr(String str) {
		if(null == str) {
			str = "";
		} else {
			str = " 　　" + UBBDecoder.decode(str.replaceAll(" ","&nbsp;").replaceAll("\r","<br>"));
		}
		str = DbUtils.charset(str);
//		str = str.replaceAll("??", "");
		return str;
	}
}
